﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using ANZ_AIM_DAL;
using ANZ_AIM_BLL.Model;
using ANZ_AIM_BLL.Utils;
using ANZ_AIM_FRAMEWORK;

namespace ANZ_AIM_BLL
{
    public class SalePIPBLL
    {
        private string ConnectionString;
        private const string TABLE_NAME = "SalePIP";
        private Dictionary<string, string> ObjectPropDict;

        public SalePIPBLL()
        {
            ConnectionString = DBHelper.GetMainMdbConnectionString();
            this.ObjectPropDict = BuildObjectPropertyMapping();
        }
      
        public void SavePIP(List<SalePIP> salePIPList)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                string command = string.Empty;
                foreach (SalePIP salePIP in salePIPList)
                {
                    command = CommandHelper.BuildInsertCommand(salePIP, dict, out parameters);
                    OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
                }
            }
            catch (Exception ex) { throw; }
        }

        public DataTable GetByBatchFileSaleInfoID(string batchFileSaleInfoID)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                SalePIP salePIP = new SalePIP() { BatchFileSaleInfoID = batchFileSaleInfoID };
                OleDbParameter[] parameters;
                var conditionDict = new Dictionary<string, string>
                    {
                        {"BatchFileSaleInfoID", "BatchFileSaleInfoID"}
                    };
                string command = CommandHelper.BuildSelectCommand(salePIP, dict, conditionDict, out parameters);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public string GetSaleCodeMapping(string saleCode)
        {
            try
            {
                string command = string.Format("Select top 1 [ProfileID] FROM {0} WHERE SaleCode = '{1}'", TABLE_NAME, saleCode);
                DataTable dt = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command).Tables[0];
                return dt.Rows.Count > 0 ? dt.Rows[0][0].ToString() : "";
            }
            catch (Exception ex) { throw; }
        }

        public DataTable Search(string personalCode, string status, string fromDate, string toDate)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                string command = string.Empty;
                command = CommandHelper.BuildSelectAllCommand(TABLE_NAME, dict);
                command += " WHERE (@personalCode IS NULL OR ProfileID = @PersonalCode)";
                command += " AND (@status IS NULL OR CurrentMonthPIPStatus=@status)";
                command += " AND Month BETWEEN @FromDate AND @ToDate;";

                OleDbParameter[] parameters = new OleDbParameter[4];

                if (string.IsNullOrEmpty(personalCode))
                {
                    parameters[0] = new OleDbParameter("@personalCode", DBNull.Value);
                }
                else
                {
                    parameters[0] = new OleDbParameter("@personalCode", personalCode);
                }
                if (string.IsNullOrEmpty(status))
                {
                    parameters[1] = new OleDbParameter("@status", DBNull.Value);
                }
                else
                {
                    parameters[1] = new OleDbParameter("@status", status);
                }

                parameters[2] = new OleDbParameter("@FromDate", fromDate);
                parameters[3] = new OleDbParameter("@ToDate", toDate);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public Dictionary<string, string> BuildObjectPropertyMapping()
        {
            Dictionary<string, string> dictionary = new Dictionary<string, string>
            {
                {"Month", "Month"},
                {"SaleCode", "SaleCode"},
                {"PersonalCode", "PersonalCode"},
                {"Role", "Role"},
                {"EmployeeFullName", "EmployeeFullName"},
                {"Level", "Level"},
                {"PIPTarget", "PIPTarget"},
                {"PIPM1Result", "PIPM1Result"},
                {"PIPM2Result", "PIPM2Result"},
                {"CurrentMonthPIPStatus", "CurrentMonthPIPStatus"},
                {"PIPMonth", "PIPMonth"},
                {"BatchFileSaleInfoID", "BatchFileSaleInfoID"},   
                {"ProfileID", "ProfileID"} 
            };
            return dictionary;
        }

    }
}
